
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserOrderSummary]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUserOrderSummary]
GO


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

------------------------
--Added Invoice Number and Invoice Date to PurPOUserOrderHeader 
--and PurJournalUserOrderHeader tables - TrackIt # 8343 05-30-2008
------------------------

CREATE  PROCEDURE [dbo].[GetUserOrderSummary]
(
	 @domainUserID				integer
 	,@transactionDateFrom 		datetime		= null			--1
 	,@transactionDateTo			datetime		= null			--2
 	,@fundNumber				varchar(6)		= null			--3
 	,@accountNumber				varchar(6)		= null			--4
 	,@accountCategoryId			integer			= null			--5
 	,@invoiceNumber				varchar(16)		= null			--6
 	,@invoiceDate				datetime		= null			--7
 	,@poNumber					varchar(16)		= null			--8
 	,@voucherNumber				varchar(16)		= null			--9
 	,@requestorName				varchar(50)		= null
 	,@requisitionNumber			varchar(30)		= null
 	,@vendorName				varchar(20)		= null			--12
 	,@orderTypeId				integer			= null
 	,@piId						integer			= null
	,@labId						integer			= null			--15
	,@chiefCodeList				varchar(255)	= null			--16
	,@amount money = null  
)
AS
BEGIN -- sp begins

 	SET NOCOUNT ON
	
	DECLARE		@purchaseOrderType 	        INTEGER
	DECLARE		@travelVoucherOrderType 	INTEGER
	DECLARE		@chequeRequestOrderType 	INTEGER
	DECLARE		@journalEntryOrderType 		INTEGER
	DECLARE		@pettyCashOrderType 		INTEGER
	SET			@purchaseOrderType  		= 1
	SET			@travelVoucherOrderType  	= 2
	SET			@chequeRequestOrderType  	= 3
	SET			@journalEntryOrderType  	= 4
	SET			@pettyCashOrderType 		= 5
	  

	--if user has view peoplesoft information for the agreement they can get to the user order summary info
	DECLARE		@viewPeopleSoftBitValue int 
	SET			@viewPeopleSoftBitValue = 512

	--Security data

	IF OBJECT_ID('tempdb..#tmpFilterFund') IS NOT NULL
		Drop Table #tmpFilterFund
	Create Table #tmpFilterFund(FundId int primary key, AccessLevel int default 0)
	
	If  ltrim(rtrim(isnull(@ChiefCodeList,'')))<>'' 
	begin
		Insert Into #tmpFilterFund
			EXEC FilterFunds @domainUserId
						,@viewPeopleSoftBitValue
						,null
						,@fundNumber
						,null
						,null
						,null
						,null
						,null
						,null
						,null
						,null
						,null
						,null
						,null
						,null
						,null
						,@piId
						,@chiefCodeList
						,null
						,null
						,null
						,null
						,null
						,null
						,null
						,null
	end
	else begin
		if isnull(@piId,0) = 0
			Insert into #tmpFilterFund
			Select af.FundId, coalesce(urc.AccessLevel,urc1.AccessLevel,0) as 'AccessLevel' From AgrAgreement_fct af
			left outer join UserResourceCache urc on urc.ResourceId = af.ResourceId and urc.FwkDomainUserId = @domainUserID and urc.ResourceTypeId = 1
			left outer join UserResourceCache urc1 on urc1.ResourceId = af.FundFwkResourceId and urc1.FwkDomainUserId = @domainUserID and urc1.ResourceTypeId = 4
			Where isnull(af.fundnumber,'') <> ''
			and (@viewPeopleSoftBitValue & coalesce(urc.AccessLevel,urc1.AccessLevel,0)) > 0
	else
			Insert into #tmpFilterFund
			Select af.FundId, coalesce(urc.AccessLevel,urc1.AccessLevel,0) as 'AccessLevel'  From AgrAgreement_fct af
			left outer join UserResourceCache urc on urc.ResourceId = af.ResourceId and urc.FwkDomainUserId = @domainUserID and urc.ResourceTypeId = 1
			left outer join UserResourceCache urc1 on urc1.ResourceId = af.FundFwkResourceId and urc1.FwkDomainUserId = @domainUserID and urc1.ResourceTypeId = 4
			Where isnull(af.fundnumber,'') <> ''
			and (@viewPeopleSoftBitValue & coalesce(urc.AccessLevel,urc1.AccessLevel,0)) > 0			
			and af.PrincipalInvestigatorId = @piId
	end
	CREATE INDEX IX_tmpFilterFund ON #tmpFilterFund(FundId)
	
	SELECT DISTINCT
		Transactions.OrderNumber,
		Transactions.RequisitionNumber,		
		Transactions.InvoiceNumber,
		Transactions.VendorName,
		uoh.Id			 	AS 'OrderId',
		uot.[Name] 			AS 'OrderType',
		uot.Id				AS 'OrderTypeId',
		uoh.TransactionDate AS 'OrderDate',
		uoh.Comment 			AS 'Comments',	
		uod.RequestorName,
		af.FundNumber,
  		Uod.amount as  'OrderAmount'      
	FROM 
		PurUserOrderHeader uoh
	
	INNER JOIN
	(
		--Get Voucher Information
		SELECT 
			vuoh.PurUserOrderHeaderId,
			vuoh.PSVoucherId 	AS 'OrderNumber',
			vuoh.InvoiceNumber,
			vuoh.InvoiceDate,
			vuoh.VendorId,
			vuoh.VendorName,
			vuoh.VendorNumber,
			'' 			AS 'RequisitionNumber'
			,vuoh.InvoiceAmount AS 'OrderAmount'
		from
			PurVoucherUserOrderHeader vuoh
		inner join
			PurUserOrderHeader uoh
		on
		   uoh.[id]= vuoh.PurUserOrderHeaderId
		where @orderTypeId IS NULL OR PurUserOrderTypeId = @orderTypeId   
			
	
		UNION ALL
	
		--Get PO information
		SELECT 
			puoh.PurUserOrderHeaderId,
			puoh.POID 		AS 'OrderNumber',
			puoh.InvoiceNumber,
			puoh.InvoiceDate,
			puoh.VendorId,
			puoh.VendorName,
			puoh.VendorNumber,
			puoh.PSRequisitionNumber AS 'RequisitionNumber'
			,puoh.TotalAmount AS 'OrderAmount'
		from
			PurPOUserOrderHeader puoh
		inner join
			PurUserOrderHeader uoh
		ON
		   uoh.[id]= puoh.PurUserOrderHeaderId
		WHERE @orderTypeId IS NULL OR PurUserOrderTypeId = @orderTypeId
			
	
		UNION ALL
	
		--Get JournalEntry Information
		SELECT 	
			juoh.PurUserOrderHeaderId,
			juoh.JournalId 	AS 'OrderNumber',
			juoh.InvoiceNumber,
			juoh.InvoiceDate,
			0	 	AS 'VendorId',
			'' 		AS 'VendorName',
			''	 	AS 'VendorNumber',
			'' 		AS 'RequisitionNumber'	
			,juoh.JournalAmount AS 'OrderAmount'
		from
			PurJournalUserOrderHeader juoh
		INNER JOIN
			PurUserOrderHeader uoh
		on
		   uoh.[id]= juoh.PurUserOrderHeaderId
		where @orderTypeId IS NULL OR PurUserOrderTypeId = @orderTypeId  
		
	) Transactions
	
	ON
		uoh.[Id] = Transactions.PurUserOrderHeaderId
	
	INNER JOIN
		PurUserOrderType uot 
	ON
		 uot.[Id] = uoh.PurUserOrderTypeId
	
	INNER JOIN
		PurUserOrderDistribution uod
	ON 
		Transactions.PurUserOrderHeaderId = uod.PurUserOrderHeaderId
	LEFT OUTER JOIN
		FinAccount fa
	ON
 		uod.AccountId = fa.[Id]
	INNER JOIN
		#tmpFilterFund acc
	ON
		acc.FundId = uod.AgrFundId
	INNER JOIN AgrFund af
	ON
		af.Id = uod.AgrFundId
	WHERE   
	(@transactionDateFrom IS  NULL OR uoh.TransactionDate >=  @transactionDateFrom)
	AND 
	(@transactionDateTo IS  NULL OR uoh.TransactionDate <=  @transactionDateTo)
	AND
	(@fundNumber IS NULL OR af.FundNumber LIKE @fundNumber+'%')
	AND
	(@accountNumber IS NULL OR fa.Number LIKE @accountNumber+'%')
	AND
	(@accountCategoryId IS NULL OR fa.FinCategoryId = @accountCategoryId)
	AND
	(@invoiceNumber IS NULL OR Transactions.InvoiceNumber LIKE @invoiceNumber+'%')
	AND
	(@invoiceDate IS NULL OR Transactions.InvoiceDate = @invoiceDate)
	AND
	(@poNumber IS NULL OR Transactions.OrderNumber LIKE @poNumber+'%')
	AND
	(@voucherNumber IS NULL OR Transactions.OrderNumber LIKE @voucherNumber+'%')
	AND
	(@requestorName IS NULL OR uod.RequestorName LIKE @requestorName+'%')
	AND
	(@requisitionNumber IS NULL OR Transactions.RequisitionNumber LIKE @requisitionNumber+'%')
	AND
	(@vendorName IS NULL OR Transactions.VendorName LIKE @vendorName+'%')
 	AND    
	 (@amount IS NULL OR Uod.Amount = @amount)    
	
	ORDER BY uoh.TransactionDate DESC

end


GO

